with tb_company_source as ( --历史同行各平台单量
    select
        ec.bill_date     --日期
         ,ec.company      --公司
         ,ec.source_code  --平台
         ,ec.source_value --历史单量
         ,di.weekday_eg   --周几
         ,ec.bill_year    --年
         ,ec.detail_flag  --明细标签 1被减数2减数
    from (
             select  bill_date,company,kk as source_code,vv as source_value
                  ,year(bill_date) as bill_year
                  ,1 as detail_flag
             from jms_dwd.dwd_express_company_bill_cnt
                 lateral view explode(map('tb',tb,'pdd',pdd,'dy',dy,'ks',ks,'other',other)) tb as kk,vv
             where (bill_date  between '2022-11-01' and '2022-12-31' or bill_date  between '2021-11-01' and '2021-12-31')
             union all
             select  bill_date,company,kk as source_code,vv as source_value
                     ,year(bill_date) as bill_year
                     ,2 as detail_flag
             from jms_dwd.dwd_express_company_bill_cnt
                 lateral view explode(map('tb',tb,'pdd',pdd,'dy',dy,'ks',ks,'other',other)) tb as kk,vv
             where ( bill_date  between date_add(concat('2021-',date_format('{{ execution_date  | cst_ds }}','MM-dd')),-6) and concat('2021-',date_format('{{ execution_date  | cst_ds }}','MM-dd'))
                or bill_date  between date_add(concat('2022-',date_format('{{ execution_date  | cst_ds }}','MM-dd')),-6) and concat('2022-',date_format('{{ execution_date  | cst_ds }}','MM-dd')))
         )ec left join jms_dim.dim_date_info_base di on ec.bill_date=di.date_mid_desc
)
--11月 各平台每天增幅
insert overwrite table jms_tmp.tmp_platform_forecast_increase_rt_dt partition(dt='{{ execution_date  | cst_ds }}')
select
    a.bill_date    as date_time           --日期 11月1日
     ,a.weekday_eg   as weekday_eg          --星期
     ,a.source_code  as source_code         --平台
     ,sum(a.source_value) as source_value_a --单量
     ,b.bill_date    as bill_date_b         --按照9月6日
     ,sum(b.source_value) as source_value_b --参照单量
     ,cast((sum(a.source_value)-sum(b.source_value))/sum(b.source_value) as decimal(16,4)) as rt --总增幅
from tb_company_source a
         left join tb_company_source b on b.detail_flag=2
    and a.company=b.company and a.source_code=b.source_code and a.weekday_eg=b.weekday_eg and a.bill_year=b.bill_year
where a.detail_flag=1
group by a.bill_date,a.weekday_eg ,a.source_code,b.bill_date
    distribute by 1
;
-----------------------------------------------------------------------------------------------------------------------------
with t_order_cnt as ( --各平台每天单量占比计算（最近7天）
    select
        date_time
         ,source_code
         ,cast(source_all_cnt/all_cnt*day_all_cnt as decimal(16,4)) as waybill_cnt
    from
        (
            select
                case WHEN t.order_source_code ='D09' then 'pdd'
                     WHEN t.order_source_code ='D67' then 'dy'
                     WHEN t.order_source_code ='D190' then 'ks'
                     WHEN t.order_source_code ='D801' then 'tb'
                     WHEN t.order_source_code ='other' then 'other'
                    end as source_code
                 ,sum(waybill_cnt) over(partition by dt) as day_all_cnt--每天总的单量
                 ,sum(waybill_cnt) over() as all_cnt--7天总单量
                 ,sum(waybill_cnt) over(partition by order_source_code) as source_all_cnt--7天各平台单量
                 ,dt as date_time
                 ,waybill_cnt
            from
                (
                    select  --每天订单量
                        if(order_source_code in ('D09','D67','D190','D801'),order_source_code,'other') as order_source_code
                         ,dt
                         ,count(1) as waybill_cnt
                    from jms_dwd.dwd_yl_oms_oms_order_incre_dt
                    where dt between date_add('{{ execution_date  | cst_ds }}',-6) and '{{ execution_date  | cst_ds }}'
                      and  is_delete=1 and cancel_time is null --订单取消
                    group by
                        if(order_source_code in ('D09','D67','D190','D801'),order_source_code,'other'),dt
                )t
        )t2
)

insert overwrite table jms_dm.dm_platform_forecast_waybill_cnt_dt partition(dt='{{ execution_date  | cst_ds }}')
select  --预测各平台单量
    concat(year(ord.date_time),substr(art.date_time,5,6)) as date_time      --预测日期
     ,art.weekday_eg    --星期
     ,ord.source_code   --平台
     ,art.rt --各公司总增幅
     ,ord.waybill_cnt   --单量
     ,cast(ord.waybill_cnt*(1+rt) as int) as forecast_waybill_cnt --预测单量
     ,year(art.date_time)  as version_flag--同比年份的
from
    jms_tmp.tmp_platform_forecast_increase_rt_dt art
    left join t_order_cnt ord on date_format(ord.date_time,'MM-dd')=date_format(art.bill_date_b,'MM-dd') and ord.source_code=art.source_code
where art.dt='{{ execution_date  | cst_ds }}'

--11月实际单量
union all

select
    dt as date_time  --日期
     ,null as weekday_eg
     ,case WHEN act.order_source_code ='D09' then 'pdd'
           WHEN act.order_source_code ='D67' then 'dy'
           WHEN act.order_source_code ='D190' then 'ks'
           WHEN act.order_source_code ='D801' then 'tb'
           WHEN act.order_source_code ='other' then 'other'
    end as source_code
     ,null as rt
     ,waybill_cnt as  waybill_cnt
     ,waybill_cnt as  forecast_waybill_cnt
     ,'2023' as version_flag
from (
         select --每天订单量
             if(order_source_code in ('D09', 'D67', 'D190', 'D801'), order_source_code, 'other') as order_source_code
              , dt
              , count(1)                                                                         as waybill_cnt
         from jms_dwd.dwd_yl_oms_oms_order_incre_dt
         where dt between '2023-11-01' and '{{ execution_date  | cst_ds }}'
           and is_delete = 1
           and cancel_time is null --订单取消
         group by if(order_source_code in ('D09', 'D67', 'D190', 'D801'), order_source_code, 'other'), dt
     ) act
    distribute by 1
;
--------------------------------------------------------------------------------------------------------------------
--网点单量预测
with t_network_bill_rt as (  --始末网点单量占比
    select
        step2.taking_code,step2.taking_name
         ,step2.deliver_code,step2.deliver_name
         ,min(step2.out_sign_days) as aging_days --out_sign_days
         ,step1.se_network_total as all_cnt
         ,step1.se_network_total/step1.bill_total as network_rt
    from jms_tmp.tmp_cainiao_active_tn_match_dt step2
             left join jms_tmp.tmp_cainiao_active_tn_history_dt step1
                       on step1.dt='{{ execution_date  | cst_ds }}' and step2.dt='{{ execution_date  | cst_ds }}'
                           and step1.sort_number=1 and step2.line_rn=1
                           and step1.taking_code = step2.taking_code
                           and step1.dispatch_code = step2.deliver_code
                           and step1.start_hm = step2.start_hm
                           and step1.end_hm = step2.end_hm
    where step1.dt='{{ execution_date  | cst_ds }}' and step2.dt='{{ execution_date  | cst_ds }}'
      and step1.sort_number=1 and step2.line_rn=1
    group by  step2.taking_code,step2.taking_name
           ,step2.deliver_code,step2.deliver_name
           ,step1.se_network_total
           ,step1.bill_total
)
insert overwrite table jms_dm.dm_network_forecast_waybill_cnt_dt partition(dt='{{ execution_date  | cst_ds }}')
select
    tt.date_time
     ,tt.network_code
     ,net.name as network_name
     ,cast(sum(tt.taking_cnt) as decimal(16,4)) as taking_cnt
     ,cast(sum(tt.deliver_cnt) as decimal(16,4))  as deliver_cnt
     ,version_flag
from
    (
        select
            case when grouping__id=5 then date_time
                 when grouping__id=10 then deliver_date
                end as date_time     --日期
             ,case when grouping__id=5 then taking_code
                   when grouping__id=10 then deliver_code
            end as network_code --网点
             ,if(grouping__id=5, sum(forecast_net_cnt),0)  as taking_cnt--订单量
             ,if(grouping__id=10, sum(forecast_net_cnt),0) as deliver_cnt--派件量
             ,version_flag
        from
            (
                select /*+ broadcast(f) */
                    f.date_time   --日期
                     --,f.weekday_eg --周几
                     ,n.taking_code --揽收网点
                     ,n.taking_name
                     ,n.deliver_code --派件网点
                     ,n.deliver_name
                     ,n.aging_days    --时效
                     ,f.forecast_waybill_cnt as forecast_all_cnt  --预测总单量
                     ,date_add(f.date_time,n.aging_days) as deliver_date --派件日期
                     ,f.forecast_waybill_cnt*n.network_rt as forecast_net_cnt --预测网点到网点单量
                     ,f.version_flag
                from t_network_bill_rt n   --t-1，网点a ，网点b，单量占比
                         cross join ( --日期预测总量
                    select
                        date(date_time) as date_time --日期
                   --,weekday_eg --周几
                   ,sum(forecast_waybill_cnt) as forecast_waybill_cnt --单量预测
                   ,version_flag
                    from
            jms_dim.dim_tab_platform_forecast_ord_cfg_base_dt
                where version_flag in ('2021','2022')
                  and dt='{{ execution_date  | cst_ds }}' and date_time is not null
                group by date_time,version_flag
            ) f
    )t
group by version_flag,date_time,deliver_date,taking_code,deliver_code
    grouping sets((version_flag,date_time,taking_code),(version_flag,deliver_date,deliver_code))
---(00101,01010) =(1+4,2+8)
)tt left join jms_dim.dim_network_whole_massage net on tt.network_code=net.code
group by date_time,network_code,net.name,version_flag

union all

--2023年实际网点货量
select
    tt.date_time
     ,tt.network_code
     ,net.name as network_name
     ,cast(sum(tt.taking_cnt) as decimal(16,4)) as taking_cnt
     ,cast(sum(tt.deliver_cnt) as decimal(16,4))  as deliver_cnt
     ,version_flag
from (
         select case
                    when grouping__id = 5 then date_time
                    when grouping__id = 10 then deliver_date
             end                                                as date_time    --日期
              , case
                    when grouping__id = 5 then taking_code
                    when grouping__id = 10 then deliver_code
             end                                             as network_code --网点
              , if(grouping__id = 5, sum(waybill_cnt), 0)  as taking_cnt--订单量
              , if(grouping__id = 10, sum(waybill_cnt), 0) as deliver_cnt--派件量
              , version_flag
         from (
                  select '2023'                               as version_flag --版本
                       , act.dt                               as date_time
                       , act.taking_code
                       , act.taking_name
                       , date_add(act.dt, nvl(tnn.aging_days, 2)) as deliver_date
                       , act.deliver_code
                       , act.deliver_name
                       ,waybill_cnt
                  from (
                           select --每天订单量
                               pick_network_code as taking_code
                                , pick_network_name as taking_name
                                , dispatch_network_code as deliver_code
                                , dispatch_network_name as deliver_name
                                , count(1)              as waybill_cnt
                                --要修改
                                , dt
                           from jms_dwd.dwd_yl_oms_oms_order_incre_dt
                           where
                             -- 要修改 暂时测试用
                             --dt between add_months('2023-11-01',-1) and add_months('2023-11-05',-1)
                               dt between '2023-11-01' and '{{ execution_date  | cst_ds }}'
                             and is_delete = 1
                             and cancel_time is null --订单取消
                             and pick_time is not null
                           group by pick_network_code,pick_network_name,dispatch_network_code,dispatch_network_name,dt
                       ) act
                           left join
                       (
                           select taking_code
                                , taking_name
                                , deliver_code
                                , deliver_name
                                , min(cast(days as int)) as aging_days --取t-1时效
                           from jms_dm.dm_cainiao_active_tn_v2_dt
                           where dt = '{{ execution_date  | cst_ds }}'
                           group by taking_code, taking_name, deliver_code, deliver_name
                       ) tnn on act.taking_code = tnn.taking_code and act.deliver_code = tnn.deliver_code
              ) t group by version_flag, date_time, deliver_date, taking_code,deliver_code
             grouping sets((version_flag,date_time,taking_code),(version_flag,deliver_date,deliver_code))
     )tt left join jms_dim.dim_network_whole_massage net on tt.network_code=net.code
group by date_time,network_code,net.name,version_flag
    distribute by 1;
